 package dbhandeling;
import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Vector;




@SuppressWarnings("unused")
public  class Crude {
	// Insert
	@SuppressWarnings("rawtypes")
	public  void insert(Object obj) throws Exception
	{
		Connection con=Connections.getMyConnection();
	    String  myvalues="";
		String columnNames="";
		String className=obj.getClass().getName();
		String[]packageNames=className.split("\\.");
		String tablename=packageNames[packageNames.length-1];
		Class clas=Class.forName(className);
		
		Field[] fields=clas.getDeclaredFields();
        
		String sql = "insert into "+tablename;
        
        HashMap<Field,Method>hashMap=this.getGetterFieldMethodPair(className);
		
        
        for(int i=0;i<fields.length;i++)
		{
			if(fields[i].getName().startsWith("auto")||fields[i].getName().startsWith("model"))
			{
				;
			}
			
			else
			{
				
				columnNames=columnNames+","+fields[i].getName(); 
				
				Method method=hashMap.get(fields[i]);
				
				
				System.out.println(method.getName());
				String  temp=  method.invoke(obj)+"";
				
				
				if(this.manageString(className, fields[i].getName()))
				{
				    
				    temp="'"+temp+"'";
				    
				   
				}
				
				
				 myvalues=myvalues+","+temp;
			}
		}
		
		// Remove the initial comas
		columnNames=columnNames.substring(1);
		myvalues=myvalues.substring(1);
		sql=sql+" ("+columnNames+" )"+" VALUES ("+ myvalues+" )";
		
	    Statement smt=con.createStatement();
	    System.out.println(sql);
	    smt.executeUpdate(sql);
		
		
}


	
	/*
	 * Incomplete method
	 */
	public  Object[] retreiveArrayOnCondition(String className,String conditionField,String conditionValue) throws Exception
	{
		
		// Get Connection Object
		Connection con=Connections.getMyConnection();  
		// Sql string to be made dynamically
		String sql=null;                               
		
		// HashMap object containing fields and corresponding setter methods
		HashMap<Field, Method> hash = null;
		try {
		  hash = this.getSetterFieldMethodPair(className);
		} catch (Exception e1) {
		
			e1.printStackTrace();
		} 
		Object[]objects=null;
	    
	      // Get the statement object to run querry
	    	Statement st=con.createStatement();   			
			
			// Paste "'" on condition value if it is a string
	    	if(manageString(className,conditionField))
				conditionValue="'"+conditionValue+"'";    
			
		    //Form the querry 
	    	String[]packageNames=className.split("\\.");
	    	String tableName=packageNames[packageNames.length-1];
	    	sql="SELECT *  from "+tableName+" WHERE "+conditionField+" = "+conditionValue;
		    
	    	ResultSet rs=st.executeQuery(sql);
		     
	    	// count the number of returned rows
	    	int count = 0;
		     while(rs.next())
		    	 count++;
		     
		     
		     
		     // initalize the object[] this is where actual objects corresponding to rows are stored
		     objects=new Object[count];
		    
		    
		    
			 // Execute the query again to take data on constructor
		     try {
				rs=st.executeQuery(sql);
				 int i=0;
			     while(rs.next())
			     {
			    	 objects[i]=this.makeObjectFromRow(className, rs, hash);
				    	i++;
			    	
			     }
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		    
		     return objects;  	
		     
	    }
		
	
	
	
	public  Object[] retreiveAll(String className) throws Exception
	{
		
		// Get Connection Object
		Connection con=Connections.getMyConnection();  
		// Sql string to be made dynamically
		String sql=null;                               
		
		// HashMap object containing fields and corresponding setter methods
		HashMap<Field, Method> hash = null;
		try {
		  hash = this.getSetterFieldMethodPair(className);
		} catch (Exception e1) {
			
			e1.printStackTrace();
		} 
		Object[]objects=null;
	    
	      // Get the statement object to run querry
	    	Statement st=con.createStatement();   			
			
			// Paste "'" on condition value if it is a string
	    	
			
		    //Form the querry 
	    	String[]packageNames=className.split("\\.");
	    	String tableName=packageNames[packageNames.length-1];
	    	sql="SELECT *  from "+tableName;
		    
	    	ResultSet rs=st.executeQuery(sql);
	    
		     
	    	// count the number of returned rows
	    	int count = 0;
		     while(rs.next())
		    	 count++;
		     
		     
		     
		     // initalize the object[] this is where actual objects corresponding to rows are stored
		     objects=new Object[count];
		     
		    
		    
			 // Execute the query again to take data on constructor
		     try {
				rs=st.executeQuery(sql);
				 int i=0;
			     while(rs.next())
			     {
			    	 
			    	 objects[i]= this.makeObjectFromRow(className,rs, hash);
			    	 i++;
			    	
			     }
			} catch (SQLException e) {
				
				e.printStackTrace();
			}
		    
		     return objects;  	
		     
	    }
		   
		    
	 
		
	
	
	public  Object retreive(String className,String conditionField,String conditionValue)throws Exception
	{
		
		// Get Connection Object
		Connection con=Connections.getMyConnection();  
		// Sql string to be made dynamically
		String sql=null;                               
		
		// HashMap object containing fields and corresponding setter methods
		HashMap<Field, Method> hash = null;
		try {
		  hash = this.getSetterFieldMethodPair(className);
		} catch (Exception e1) {
		
			e1.printStackTrace();
		} 
		
		// Get the statement object to run querry
    	Statement st=con.createStatement();   			
		
		// Paste "'" on condition value if it is a string
    	if(manageString(className,conditionField))
			conditionValue="'"+conditionValue+"'";    
		
	    //Form the querry 
    	
    	String[]packageNames=className.split("\\.");
    	String tableName=packageNames[packageNames.length-1];
    	sql="SELECT *  from "+tableName+" WHERE "+conditionField+" = "+conditionValue;
    	
    	ResultSet rs=st.executeQuery(sql);
    	rs.next();
    	 
	   
	    Object obj=this.makeObjectFromRow(className, rs, hash);
	    return obj;
    }
	
	
	
	
	
	
	//delete
	public  void delete(String className,String conditionField,String conditionValue)
	{
		Connection con=Connections.getMyConnection();
		
		String sql=null;
		try {
			Statement st=con.createStatement();
			
			
			
			if(manageString(className,conditionField))
				conditionValue="'"+conditionValue+"'";
			String[]packageNames=className.split("\\.");
			String tableName=packageNames[packageNames.length-1];
		     sql=" DELETE FROM "+tableName+" where "+conditionField+ " = " +conditionValue;
		     st.execute(sql);
		} catch (SQLException e) {
			
			
			e.printStackTrace();
		}
		
		
	}
	
	//update
	
	public  void update(String className,String requiredField,String requiredValue,String conditionField,String conditionValue)
	{
		Connection con=Connections.getMyConnection();
		String sql=null;
		try {
			Statement st=con.createStatement();
			
			if(manageString(className,requiredField))
				requiredValue="'"+requiredValue+"'";
			
			if(manageString(className,conditionField))
				conditionValue="'"+conditionValue+"'";
			String[]packageNames=className.split("\\.");
			String tableName=packageNames[packageNames.length-1];
			sql="UPDATE "+tableName+" set "+requiredField+" = "+ requiredValue + " WHERE "+
			
			
			conditionField+ " = "+conditionValue;
			
		     st.execute(sql);
		} catch (SQLException e) {
			
			System.out.println(sql);
			e.printStackTrace();
		}
		
}
	

	
	
@SuppressWarnings({ "rawtypes", "unchecked" })
private Object makeObjectFromRow(String className, ResultSet rs,HashMap<Field,Method> hash) throws ClassNotFoundException, SecurityException, NoSuchMethodException, IllegalArgumentException, IllegalAccessException, InvocationTargetException, SQLException, InstantiationException
	
	{
		// Constructor varaiable to be used to create Object
		Constructor constructor=null; 
		
		// Take class object respective to the class name passed by user
   	    Class clas;
		clas = Class.forName(className);
		
	    // Create constructor object for the class
   	    constructor =clas.getConstructor(new Class[]{});
   	    
   	// Create new instance of the object  this object parameters should 
   	 // be filled from database
   	 Object obj=constructor.newInstance();
        
   	 //start getting fields from hash ,from the data type of the field decide 
   	//which method of rs is to be called.
   	Iterator<Entry<Field, Method>> it = hash.entrySet().iterator();
   	
   	
   	while (it.hasNext()) {
           Map.Entry<Field, Method> pairs = (Map.Entry<Field, Method>)it.next();
          
           Field tempField= pairs.getKey();
           Method tempMethod=pairs.getValue();
           
           
           if(tempField.getName().contains("model"))
        	   continue;
           if(tempField.getType().getName().contains("String"))
           {
           	tempMethod.invoke(obj, rs.getString(tempField.getName()));
           }
           
           
           if(tempField.getType().getName().contains("double"))
           {
           	tempMethod.invoke(obj, rs.getDouble(tempField.getName()));
           }
           
           if(tempField.getType().getName().contains("int"))
           {
           	tempMethod.invoke(obj, rs.getInt(tempField.getName()));
           }
           
           if(tempField.getType().getName().contains("Date"))
        		tempMethod.invoke(obj, rs.getDate(tempField.getName()));
           
           
   	
   	}
   	    
   	 return obj;   
		
		
		
	}

public int getAutoGenratedKey(String tableName,String fieldName)

{
	Connection con=Connections.getMyConnection();
	int key=0;
	String sql = null;
	
	tableName=tableName.split("\\.")[1];
	try {
		Statement st=con.createStatement();
		
		sql="SELECT MAX ("+fieldName+")"+" FROM "+tableName;
		
		ResultSet rs=st.executeQuery(sql);
		rs.next();
		key=rs.getInt(1);
		
	} catch (SQLException e) {
		
		e.printStackTrace();
	}
	
	
	return key;
	
}
	
//Data type returning method
	public   boolean manageString(String tableName,String attributeName)
	{
		                String type=null;
			           try {
						
			        	   @SuppressWarnings("rawtypes")
						Class c=Class.forName(tableName);
			        	   Field f=c.getDeclaredField(attributeName);
			        	   
			        	  type=f.getType().toString();
						  
			        	   if(type.contains("String")||type.contains("Date")||type.contains("Timestamp"))
			        		   return true;
					} 
			           
			       catch (Exception e) {
						
						e.printStackTrace();
					}
			           
			return false;
		
	}
	
private String[] manageStringColums(String tableName,String[]values)
	{
		Connection conn=Connections.getMyConnection();
		Statement st;
		String[]data=null;
		try {
			st = conn.createStatement();
			
			ResultSet rset = st.executeQuery("SELECT * FROM \""+tableName+"\"");
			
			ResultSetMetaData md = rset.getMetaData();
			
			String temp;
			int k=0;
			
			for (int i=1; i<=md.getColumnCount(); i++)
			{
			    temp=md.getColumnClassName(i);
			    
			    if(temp.contains("String"))
			    {
			    	values[k]="'"+values[k]+"'";
			    	k++;
			    }
			}
		} catch (SQLException e) {
		
			e.printStackTrace();
		}
		
			
		
		return values;
		
		
	
	}
	

@SuppressWarnings("rawtypes")
private HashMap<Field,Method> getSetterFieldMethodPair(String classname) throws Exception
	{
		
		Class t = Class.forName(classname);
		Field[] f=t.getDeclaredFields();
		
		Method[] methods=t.getMethods();
		
		Method[] setter=null;
		int count=0;
		for(Method temp:methods)
		{
		   if(isSetter(temp))
			   count++;
		}
		setter=new Method[count];
		int i=0;
		for(Method temp:methods)
			   if(isSetter(temp))
			   {
				   
				   setter[i]=temp;
				   i++;
			   }
		
		
		HashMap<Field, Method> hm=new HashMap<Field, Method>();
		
		String tempField,tempMethod;
		
		for( i=0;i<f.length;i++)
		{
			
			for(int j=0;j<setter.length;j++)
			{
				
				tempField=f[i].getName().toLowerCase();
				tempMethod=setter[j].getName().toLowerCase();
				
				if(tempMethod.endsWith(tempField))
				{
					
					hm.put(f[i], setter[j]);
					break;
				}
			}
		}
		return hm;
	}

	
private HashMap<Field,Method> getGetterFieldMethodPair(String classname) throws Exception
	{
		@SuppressWarnings("rawtypes")
		Class t = Class.forName(classname);
		
		Field[] f=t.getDeclaredFields();
		
		Method[] methods=t.getMethods();
		
		Method[] setter=null;
		int count=0;
		for(Method temp:methods)
		{
		   if(isGetter(temp))
			   count++;
		}
		setter=new Method[count];
		int i=0;
		for(Method temp:methods)
			   if(isGetter(temp))
			   {
				   
				   setter[i]=temp;
				   i++;
			   }
		
		
		HashMap<Field, Method> hm=new HashMap<Field, Method>();
		
		String tempField,tempMethod;
		
		for( i=0;i<f.length;i++)
		{
			
			for(int j=0;j<setter.length;j++)
			{
				
				tempField=f[i].getName().toLowerCase();
				tempMethod=setter[j].getName().toLowerCase();
				
				if(tempMethod.endsWith(tempField))
				{
					
					hm.put(f[i], setter[j]);
					break;
				}
			}
		}
		return hm;
	}

private static Vector<Method> getGetter(String className) throws ClassNotFoundException
	{
	   @SuppressWarnings("rawtypes")
	Class clas=Class.forName(className);
	   Method[]temp=clas.getDeclaredMethods();
	   Vector<Method>methods=new Vector<Method>();
	   for(int i=0;i<temp.length;i++)
	      {
		         if(isGetter(temp[i]))
		        	 methods.add(temp[i]);
	     }
	   
	   return methods;
	
	   
	}
	
	
private static Vector<Method> getSetter(String className) throws ClassNotFoundException
	{
	   @SuppressWarnings("rawtypes")
	Class clas=Class.forName(className);
	   Method[]temp=clas.getDeclaredMethods();
	   Vector<Method>methods=new Vector<Method>();
	   for(int i=0;i<temp.length;i++)
	      {
		         if(isSetter(temp[i]))
		        	 methods.add(temp[i]);
	     }
	   
	   return methods;
	
	   
	}
	
public static boolean isGetter(Method method){
		  if(method.getName().startsWith("get")) return true;
		  if(method.getParameterTypes().length != 0)   return false;  
		  if(void.class.equals(method.getReturnType())) return false;
		      return true;
		}
	
	public static boolean isSetter(Method method){
		  if(!method.getName().startsWith("set")) return false;
		  if(method.getParameterTypes().length != 1) return false;
		  return true;
		}
	
	
	@SuppressWarnings({ "null", "unchecked" })
	public static Object creteObjectFromResultSet(ResultSet resultSet,String className,int count)

	{
		// initalize the object[] this is where actual objects corresponding to rows are stored
	    Object objects=new Object[count];
	    @SuppressWarnings("rawtypes")
		Constructor constructor=null;
	    Object obj=null;
	    HashMap<Field,Method> hash=null;
	     
	     try {
			
			// Take class object respective to the class name passed by user
	    	 @SuppressWarnings("rawtypes")
			Class clas=Class.forName(className);
	    	 
			 // Create constructor object for the class
	    	 constructor =clas.getConstructor(new Class[]{});
			 
	    	 				
			
		} catch (Exception e) {
			
			e.printStackTrace();
		}
	     int i=0;
	     try {
			while(resultSet.next())
			 {
				
				// Create new instance of the object  this object parameters should 
				 // be filled from database
				 obj=constructor.newInstance();
				
			    
				 //start getting fields from hash ,from the data type of the field decide 
				//which method of rs is to be called.
				Iterator<Entry<Field, Method>> it = hash.entrySet().iterator();
				
				
				while (it.hasNext()) {
			        Map.Entry<Field, Method> pairs = (Map.Entry<Field, Method>)it.next();
			       
			        Field tempField= pairs.getKey();
			        Method tempMethod=pairs.getValue();
			        
			        it.remove(); // avoids a ConcurrentModificationException
			        
			        if(tempField.getType().getName().contains("String"))
			        {
			        	tempMethod.invoke(obj, resultSet.getString(tempField.getType().getName()));
			        }
			        
			        
			        if(tempField.getType().getName().contains("double"))
			        {
			        	tempMethod.invoke(obj, resultSet.getDouble(tempField.getType().getName()));
			        }
			        
			        if(tempField.getType().getName().contains("int"))
			        {
			        	tempMethod.invoke(obj, resultSet.getInt(tempField.getType().getName()));
			        }
				}
}
		} catch (InstantiationException e) {
			
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			
			e.printStackTrace();
		} catch (IllegalArgumentException e) {
		
			e.printStackTrace();
		} catch (InvocationTargetException e) {
		
			e.printStackTrace();
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
	 return obj;    
	}
	
	
	
	
}


